package cc.bookcloud.tool.ocr.util;

import cc.bookcloud.tool.ocr.common.dbscript.ExcelTableFieldRequVo;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.util.StringUtils;
import org.springframework.web.multipart.MultipartFile;

import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.InputStream;
import java.lang.reflect.Field;
import java.math.BigDecimal;
import java.util.*;
import java.util.stream.Collectors;

public class ExcelUtil {


    /**
     * desc 解析上传的Excel文件的数据
     *
     * @param multipartFile:
     * @return Map<String, Object>
     * @author oycq
     * @date 2022/5/16 13:41
     */
    public static Map<String, Object> conversion(MultipartFile multipartFile) {
        try {
            Map<String, Object> map = new HashMap<>();
            Workbook workbook = readExcel(multipartFile);
            Field[] fields = ExcelTableFieldRequVo.class.getDeclaredFields();
            Field[] newFields = new Field[fields.length - 2];
            int m = 0;
            for (Field field : fields) {
                if (!(field.getName().equals("nullVal") || field.getName().equals("primary"))) {
                    newFields[m++] = field;
                }
            }
            fields = newFields;

            List<ExcelTableFieldRequVo> list = new ArrayList<>();
            String tableName = null;
            String tableDesc = null;
            //是否允许为空和是否主键单元格可以不填会导致匹配错误，这里记录单元格位置
            int isNullIndex = 0;
            int isPrimaryIndex = 0;
            int sheetCount = workbook.getNumberOfSheets();
            //遍历Sheet
            for (int i = 0; i < 1; i++) {
                Sheet sheet = workbook.getSheetAt(i);
                //得到每个Sheet的行数,此工作表中包含的最后一行(Row的index是从0开始的)
                int rowCount = sheet.getLastRowNum();
                //遍历Row
                for (int j = 0; j <= rowCount; j++) {
                    //得到Row
                    Row row = sheet.getRow(j);
                    if (row == null) {
                        continue;
                    }
                    //得到每个Row的单元格数
                    int cellCount = row.getLastCellNum();
                    int count = 0;
                    ExcelTableFieldRequVo tableFieldInfo = new ExcelTableFieldRequVo();
                    for (int k = 0; k < cellCount; k++) {
                        Cell cell = row.getCell(k);

                        //=================================过滤并取基本信息数据 开始================================
                        //得到单元格里的值
                        String cellFormatValue = String.valueOf(getCellFormatValue(cell));
                        //表名，描述单元格特殊处理
                        if (Objects.equals(cellFormatValue, "表名")) {
                            for (int l = 0; l < cellCount; l++) {
                                int flagIndex = k;
                                while (StringUtils.isEmpty(tableName)) {
                                    tableName = String.valueOf(getCellFormatValue(row.getCell(++flagIndex)));
                                }
                                int num = 0;
                                //描述单元格原则上描述要有值
                                while (StringUtils.isEmpty(tableDesc) && num < Byte.MAX_VALUE) {
                                    String desc = String.valueOf(getCellFormatValue(row.getCell(++flagIndex)));
                                    if (!Objects.equals(desc, "描述")) {
                                        tableDesc = desc;
                                    }
                                    num++;
                                }
                            }
                            break;
                        }
                        if (Objects.equals(cellFormatValue, "是否允许空")) {
                            isNullIndex = k;
                            continue;
                        }
                        if (Objects.equals(cellFormatValue, "是否主键")) {
                            isPrimaryIndex = k;
                            continue;
                        }
                        if (/*Objects.equals(cellFormatValue, "字段名") ||*/ Objects.equals(cellFormatValue, "说明：") ||
                                Objects.equals(cellFormatValue, "索引描述：")) {
                            break;
                        }
                        //=================================过滤并取基本信息数据 结束================================


                        //取到单元格匹配的值赋值给TableFieldInfo实体类并添加到list集合
                        if (!StringUtils.isEmpty(cellFormatValue) || (k == isNullIndex) || (k == isPrimaryIndex)) {
                            if (k == isNullIndex) {
                                Field isNull = null;
                                try {
                                    isNull = tableFieldInfo.getClass().getDeclaredField("nullVal");
                                } catch (NoSuchFieldException e) {
                                    e.printStackTrace();
                                }
                                isNull.setAccessible(true);
                                if ("是".equals(cellFormatValue) || StringUtils.isEmpty(cellFormatValue)) {
                                    isNull.set(tableFieldInfo, "是");
                                } else {
                                    isNull.set(tableFieldInfo, "否");
                                }
                            } else if (k == isPrimaryIndex) {
                                Field isPrimary = null;
                                try {
                                    isPrimary = tableFieldInfo.getClass().getDeclaredField("primary");
                                } catch (NoSuchFieldException e) {
                                    e.printStackTrace();
                                }
                                isPrimary.setAccessible(true);
                                if ("是".equals(cellFormatValue)) {
                                    isPrimary.set(tableFieldInfo, "是");
                                } else {
                                    isPrimary.set(tableFieldInfo, "否");
                                }
                            } else {
                                Field field = fields[count++];
                                field.setAccessible(true);
                                //做简单处理
                                if (field.getName().equals("len")) {
                                    if (!cellFormatValue.contains("长度") &&  !cellFormatValue.contains("备注")) {
                                        field.set(tableFieldInfo, new BigDecimal(cellFormatValue).intValue());
                                    }
                                } else {
                                    field.set(tableFieldInfo, cellFormatValue);
                                }
                            }
                        }
                    }
                    list.add(tableFieldInfo);
                }
            }
            list = list.stream().filter(x -> x != null &&
                    !"字段名".equals(x.getColumn()) && !"字段描述".equals(x.getColumn())
                    && x.getColumn() != null
            ).collect(Collectors.toList());
            map.put("tableName", tableName);
            map.put("tableDesc", tableDesc);
            map.put("data", list);
            return map;
        } catch (IllegalAccessException e) {
            e.printStackTrace();
            return null;
        }
    }


    public static Workbook readExcel(MultipartFile multipartFile) {
        String originalFilename = multipartFile.getOriginalFilename();
        if (originalFilename == null || originalFilename.equals("")) {
            return null;
        }
        //得到文件后缀
        String suffix = originalFilename.substring(originalFilename.lastIndexOf("."));
        System.out.println(suffix);
        try {
            InputStream is = multipartFile.getInputStream();
            if (".xls".equals(suffix)) {
                return new HSSFWorkbook(is);
            }
            if (".xlsx".equals(suffix)) {
                return new XSSFWorkbook(is);
            }
            return null;
        } catch (FileNotFoundException e) {
            e.printStackTrace();
            System.out.println("文件没有找到");
        } catch (IOException e) {
            e.printStackTrace();
            System.out.println("发生io异常");
        }
        return null;
    }

    public static Object getCellFormatValue(Cell cell) {
        /*Object cellValue;
        if (cell != null) {
            //判断cell类型
            switch (cell.getCellType()) {
//                //空值单元格
//                case Cell.CELL_TYPE_BLANK: {
//                    cellValue = "";
//                    break;
//                }
//                //数值型单元格 getNumericCellValue()以数字形式获取单元格的值。
//                case Cell.CELL_TYPE_NUMERIC: {
//                    //判断cell是否为日期格式
//                    if (DateUtil.isCellDateFormatted(cell)) {
//                        //转换为日期格式YYYY-mm-dd
//                        //cellValue = cell.getDateCellValue();
//                        Date date = cell.getDateCellValue();
//                        SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
//                        cellValue = dateFormat.format(date);
//                    } else {
//                        //数字
//                        cellValue = cell.getNumericCellValue();
//                    }
//                    break;
//                }
//                //公式型单元格getCellFormula()返回单元格的公式
//                case Cell.CELL_TYPE_FORMULA: {
//                    System.out.println(cell.getCellFormula());
//                    cellValue = String.valueOf(cell.getNumericCellValue());
//                    break;
//                }
//                //字符串单元格
//                case Cell.CELL_TYPE_STRING: {
//                    cellValue = cell.getRichStringCellValue().getString();
//                    break;
//                }
//                //布尔值型单元格
//                case Cell.CELL_TYPE_BOOLEAN: {
//                    cellValue = String.valueOf(cell.getBooleanCellValue());
//                    break;
//                }
                default:
                    cellValue = "";
            }
        } else {
            cellValue = "";
        }
        return cellValue;*/
        return null;
    }
}